from flask import Blueprint
from flask import request,jsonify
import sqlite3
import uuid

activity_blue = Blueprint('activity', __name__)

def read_table():
    """返回activity表的所有数据"""
    """返回[(id,title,img,content,type,start,end,scope,position,figure,number,status,pattern,time,type),...]"""
    # 读取数据库表
    try:
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        sql = '''SELECT * FROM activity'''
        c.execute(sql) # 执行sql语句
        result = c.fetchall() # 获取结果
        conn.close() # 关闭数据库连接
        return result
    except:
        return '数据库出错！'

@activity_blue.route('/deleteActivities', methods=['GET'])
def delete_activity():
    try:
        id = request.args['id']
        sql = '''DELETE FROM activity WHERE id=?'''
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        c.execute(sql, (id,))
        conn.commit()
        conn.close()
        return jsonify({'msg': '删除成功', 'code': 200, 'data': {}})
    except Exception as e:
        print(e)
        return jsonify({'msg': '删除失败', 'code': 200, 'data': str(e)})

@activity_blue.route('/addActivities', methods=['POST'])
def add_activity():
    # 获取表单数据
    try:
        title = request.form['title']
        img = request.files['img'] # 图片源文件
        content = request.form['content']
        type = request.form['type']
        start = request.form['start']
        end = request.form['end']
        scope = request.form['scope']
        position = request.form['position']
        figure = request.form['figure']
        number = request.form['number']
        status = request.form['status']
        pattern = request.form['pattern']
        setting = request.form['setting']
        print("获取数据：", title, img, content, type, start, end, scope, position, figure, number, status, pattern)

        # 保存图片并获取图片url
        folder_path = '/home/files/gry' # 主文件夹路径
        img_name = str(uuid.uuid1()) # 图片名
        img_path = folder_path + '/' + img_name + '.jpg' # 图片路径
        img.save(img_path)
        img = img_name+'.jpg' # 图片url
    


        # 数据持久化
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        sql = '''INSERT INTO activity (title,img,content,type,start,end,scope,position,figure,number,status,pattern,setting) 
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)'''
        c.execute(sql, (title, img, content, type, start, end, scope, position, figure, number, status, pattern, setting))
        conn.commit()
        conn.close()
        return jsonify({'msg': '添加成功', 'code': 200, 'data': {}})
    except Exception as e:
        print(e)
        return jsonify({'msg': '添加失败', 'code': 200, 'data': str(e)})

@activity_blue.route('/updateActivities', methods=['POST'])
def update_activity():
    # 获取表单数据
    id = request.form['id']
    title = request.form['title']
    # img = request.form['img']
    content = request.form['content']
    type = request.form['type']
    start = request.form['start']
    end = request.form['end']
    scope = request.form['scope']
    position = request.form['position']
    figure = request.form['figure']
    number = request.form['number']
    status = request.form['status']
    pattern = request.form['pattern']
    setting = request.form['setting']
    # 修改数据
    try:
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        sql = '''UPDATE activity SET title=?,content=?,type=?,start=?,end=?,scope=?,position=?,figure=?,number=?,status=?,pattern=?,setting=? WHERE id=?'''
        c.execute(sql, (title, content, type, start, end, scope, position, figure, number, status, pattern, setting,id))
        conn.commit()
        conn.close()
        return jsonify({'msg': '修改成功', 'code': 200, 'data': {}})
    except Exception as e:
        print(e)
        return jsonify({'msg': '修改失败', 'code': 200, 'data': str(e)})

@activity_blue.route('/getAllActivities', methods=['GET'])
def get_all_activity():
    """获取所有活动的所有信息，返回key-value形式，为后台管理页面服务"""
    res = read_table()
    if res == '数据库出错！':
        return jsonify({'msg': '获取失败，数据库出错', 'code': 500, 'data': {}})
    else:
        data = []
        for i in res:
            id, title, img, content, type, start, end, scope, position, figure, number, status, pattern,setting = i
            data.append({'id': id, 'title': title, 'img': img, 'content': content, 'type': type, 'start': start, 'end': end, 'scope': scope, 'position': position, 'figure': figure, 'number': number, 'status': status, 'pattern': pattern, 'setting': setting})
        return jsonify({'msg': '获取成功', 'code': 200, 'data': data})

@activity_blue.route('/getActivities', methods=['GET'])
def get_activity():
    """获取所有活动的(id,title,content,img,type),返回key-value形式"""
    # 连接数据库
    conn = sqlite3.connect('contest.db')
    c = conn.cursor()
    sql = '''SELECT id,title,content,img,type FROM activity''' # 获取所有活动的(id,title,content,img,type)
    c.execute(sql)
    res = c.fetchall()
    conn.commit()
    conn.close()
    if res == '数据库出错！':
        return jsonify({'msg': '获取失败，数据库出错', 'code': 500, 'data': {}})
    else:
        data = []
        for i in res:
            id, title,content, img, type = i
            data.append({'id': id,'title':title, 'content': content, 'img': img, 'type': type})
        return jsonify({'msg': 'activities获取成功', 'code': 200, 'data': data})

@activity_blue.route('/queryActivities', methods=['GET'])
def query_activity():
    """获取指定活动的所有信息，返回元组形式"""
    # 请求单数据
    try:
        id = request.args.get('id')
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        sql = '''SELECT * FROM activity WHERE id=?''' # 获取指定活动的所有信息
        result = c.execute(sql, (id,))
        data = result.fetchall()
        conn.commit()
        conn.close()
        return jsonify({'msg': '请求成功', 'code': 200, 'data': data[0]})
    except Exception as e:
        print(e)
        return jsonify({'msg': '请求失败', 'code': 200, 'data': str(e)})
